/**
 * ExcelUtil.java
 * Description: Excel文件操作类
 * Created By Swiftly
 * Created On Dec 2, 2008
 * Version: 1.0
 *
 **/
package com.ys.malini.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.sql.ResultSetMetaData;
import java.util.List;
import java.util.ListIterator;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


public class ExcelUtil {
	/**
	 * 根据list的内容来生成excel文件
	 * 
	 * @param fileName
	 *            excel文件名
	 * @param list
	 *            包含数据vo的list
	 * @param methods
	 *            vo的get方法
	 * @param cellTitle
	 *            列的描述
	 * @return
	 */
	public static boolean createExcelFromBeanList(String fileName, List list,
			String[] methods, String[] cellTitle) {
		return createExcelFromBeanList(fileName, "sheet0", list, methods,
				cellTitle);
	}

	/**
	 * 根据list的内容来生成excel文件
	 * 
	 * @param fileName
	 * @param sheetName
	 * @param list
	 * @param methods
	 * @param cellTitle
	 * @return
	 */
	public static boolean createExcelFromBeanList(String fileName,
			String sheetName, List list, String[] methods, String[] cellTitle) {
		return createExcelFromBeanListAsFile(fileName, sheetName, list,
				methods, cellTitle, null);
	}

	/**
	 * 根据list的内容来生成excel文件
	 * 
	 * @param fileName
	 * @param sheetName
	 *            工作表名
	 * @param beanMap
	 * @param list
	 * @return
	 */
	public static boolean createExcelFromBeanListAsFile(String fileName,
			String sheetName, List list, String[] methods, String[] cellTitle,
			int[] cellWidth) {

		boolean ok = true;

		if (StringUtils.isEmpty(fileName) || list == null || methods == null
				|| cellTitle == null || methods.length != cellTitle.length)
			return false;

		FileOutputStream fileOutputStream = null;
		try {
			File exlFile = new File(fileName);
			if (!exlFile.exists()) {
				exlFile.createNewFile();
			}
			fileOutputStream = new FileOutputStream(exlFile);

			ok = createExcelFromBeanList(fileName, sheetName, list, methods,
					cellTitle, cellWidth, fileOutputStream);
		} catch (Exception e) {
			ok = false;
			e.printStackTrace();
		} finally {
			try {
				if (fileOutputStream != null) {
					fileOutputStream.close();
					fileOutputStream = null;
				}
			} catch (Exception e) {
			}
		}

		return ok;
	}

	/**
	 * 根据list的内容来生成excel文件到输出流outStream
	 * 
	 * @param fileName
	 * @param sheetName
	 * @param list
	 * @param methods
	 * @param cellTitle
	 * @param outStream
	 * @return
	 */
	public static boolean createExcelFromBeanList(String fileName,
			String sheetName, List list, String[] methods, String[] cellTitle,
			OutputStream outstream) {
		return createExcelFromBeanList(fileName, sheetName, list, methods,
				cellTitle, null, outstream);
	}

	/**
	 * 根据list的内容来生成excel文件到输出流outStream
	 * 
	 * @param fileName
	 *            文件名
	 * @param sheetName
	 *            sheet名
	 * @param list
	 *            内容List
	 * @param methods
	 *            List中bean的get方法名
	 * @param cellTitle
	 *            表头
	 * @param cellWidth
	 *            列宽
	 * @param outstream
	 *            输出流
	 * @return
	 */
	public static boolean createExcelFromBeanList(String fileName,
			String sheetName, List list, String[] methods, String[] cellTitle,
			int[] cellWidth, OutputStream outstream) {
		boolean ok = true;

		if (StringUtils.isEmpty(fileName) || list == null || methods == null
				|| cellTitle == null || methods.length != cellTitle.length)
			return false;

		if (outstream == null)
			return false;

		WritableWorkbook wwb = null;
		try {
			WorkbookSettings setting = new WorkbookSettings();
			setting.setEncoding("GBK");

			wwb = Workbook.createWorkbook(outstream, setting);
			WritableSheet ws = wwb.createSheet(sheetName, 0);

			// 写表头描述
			int row = 0;
			WritableCellFormat cellTitleFormat = new WritableCellFormat();
			cellTitleFormat.setAlignment(Alignment.CENTRE);
			cellTitleFormat.setFont(new WritableFont(WritableFont.TIMES, 8,
					WritableFont.BOLD));

			for (int i = 0, len = cellTitle.length; i < len; i++) {
				Label label = new Label(i, row, cellTitle[i], cellTitleFormat);
				ws.addCell(label);

				if (cellWidth != null)
					ws.setColumnView(i, cellWidth[i]);
			}

			row += 1;

			// 写值
			ListIterator li = list.listIterator();
			while (li.hasNext()) {
				Object bean = li.next();

				for (int i = 0, len = methods.length; i < len; i++) {
					String methodName = methods[i];
					Method method = bean.getClass().getMethod(methodName);

					Object rst = method.invoke(bean, null);

					Label label = new Label(i, row, rst != null ? rst
							.toString() : "");

					ws.addCell(label);
				}

				row += 1;
			}

			wwb.write();
		} catch (Exception e) {
			ok = false;
			e.printStackTrace();
		} finally {
			if (wwb != null) {
				try {
					wwb.close();
				} catch (Exception e) {

				}
			}
		}

		return ok;
	}

//	/**
//	 * 从查询结果集中生成excel文件
//	 * 
//	 * @param resultSetWrap
//	 * @param outstream
//	 * @param sheetName
//	 * @return
//	 */
//	public static boolean createExcelFromResultWrap(
//			ResultSetWrap resultSetWrap, OutputStream outstream,
//			String sheetName) {
//		boolean ok = true;
//
//		if (resultSetWrap == null || outstream == null)
//			return false;
//
//		WritableWorkbook wwb = null;
//
//		try {
//			WorkbookSettings setting = new WorkbookSettings();
//			setting.setEncoding("GBK");
//
//			wwb = Workbook.createWorkbook(outstream, setting);
//			WritableSheet ws = wwb.createSheet(sheetName, 0);
//
//			ResultSetMetaData metaData = resultSetWrap.getMetaData();
//
//			int len = metaData.getColumnCount();
//
//			// 写表头描述
//			int row = 0;
//
//			for (int i = 1; i <= len; i++) {
//				Label label = new Label(i - 1, row, metaData.getColumnName(i));
//
//				ws.addCell(label);
//			}
//			row += 1;
//
//			// 写值
//			while (resultSetWrap.next()) {
//				for (int i = 1; i <= len; i++) {
//					String rst = resultSetWrap.getFormatValue(i, metaData
//							.getColumnType(i));
//
//					Label label = new Label(i - 1, row, rst != null ? rst
//							.toString() : "");
//
//					ws.addCell(label);
//				}
//
//				row += 1;
//			}
//
//			wwb.write();
//		} catch (Exception e) {
//			ok = false;
//			e.printStackTrace();
//		} finally {
//			if (wwb != null) {
//				try {
//					wwb.close();
//				} catch (Exception e) {
//
//				}
//			}
//		}
//
//		return ok;
//	}

	public static void main(String[] args) {
/*		String fileName = "c:/test.xls";
		String sheetName = "测试";

		List list = new ArrayList();

		for (int i = 0; i < 10; i++) {
			NoticeAndRemindVo vo = new NoticeAndRemindVo();
			vo.setBxzh_no("2009310000" + i);
			vo.setBbr_name("张三");
			vo.setTbr_dz("广东省河源市连平县陂头镇夏田乡");

			list.add(vo);
		}

		String[] methods = new String[] { "getBxzh_no", "getBbr_name",
				"getTbr_dz" };
		String[] cellTitle = new String[] { "当前保单号", "姓名", "地址" };

		int[] cellWidth = new int[] { 15, 10, 50 };

		ExcelUtil.createExcelFromBeanListAsFile(fileName, sheetName, list,
				methods, cellTitle, cellWidth);*/
	}
}
